const mysql = require('mysql2/promise');

/**
 * 创建MySQL 5.5兼容的表结构
 */
async function createMySQL55CompatibleTables() {
    console.log('🔧 创建MySQL 5.5兼容的表结构...');

    const connection = await mysql.createConnection({
        host: '43.143.253.188',
        port: 3306,
        user: 'nextjs',
        password: 'yEAiRE2jcxTyRBy5',
        database: 'nextjs',
        charset: 'utf8mb4'
    });

    try {
        // 设置MySQL会话配置
        await connection.execute("SET SESSION sql_mode = ''");
        await connection.execute("SET SESSION time_zone = '+08:00'");
        await connection.execute("SET SESSION FOREIGN_KEY_CHECKS=0");
        console.log('✅ MySQL会话配置设置完成');

        // 创建基础表结构
        const tables = [
            // 用户表
            `CREATE TABLE IF NOT EXISTS users (
                id int NOT NULL AUTO_INCREMENT,
                mobile varchar(32) NOT NULL,
                passwordHash varchar(64) NOT NULL,
                channel varchar(64) NULL,
                inviteCode varchar(64) NULL,
                created_at datetime NOT NULL COMMENT '创建时间',
                updated_at datetime NULL COMMENT '更新时间',
                PRIMARY KEY (id),
                UNIQUE KEY mobile (mobile)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表'`,

            // 角色表
            `CREATE TABLE IF NOT EXISTS characters (
                id int NOT NULL AUTO_INCREMENT,
                userId int NOT NULL,
                name varchar(50) NOT NULL,
                level int NOT NULL DEFAULT 1,
                experience bigint NOT NULL DEFAULT 0,
                created_at datetime NOT NULL COMMENT '创建时间',
                updated_at datetime NULL COMMENT '更新时间',
                PRIMARY KEY (id),
                KEY userId (userId)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色表'`,

            // 角色技能表
            `CREATE TABLE IF NOT EXISTS character_skills (
                id int NOT NULL AUTO_INCREMENT,
                characterId int NOT NULL,
                skillId int NOT NULL,
                level int NOT NULL DEFAULT 1,
                obtainedAt datetime NOT NULL COMMENT '获得时间',
                created_at datetime NOT NULL COMMENT '创建时间',
                updated_at datetime NULL COMMENT '更新时间',
                PRIMARY KEY (id),
                KEY characterId (characterId)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='角色技能表'`,

            // 基础物品表
            `CREATE TABLE IF NOT EXISTS basic_items (
                id int NOT NULL AUTO_INCREMENT,
                name varchar(100) NOT NULL,
                description text NULL,
                type int NOT NULL,
                price int NOT NULL DEFAULT 0,
                created_at datetime NOT NULL COMMENT '创建时间',
                updated_at datetime NULL COMMENT '更新时间',
                PRIMARY KEY (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='基础物品表'`,

            // 装备基础表
            `CREATE TABLE IF NOT EXISTS equipment_base (
                id int NOT NULL AUTO_INCREMENT,
                name varchar(100) NOT NULL,
                type int NOT NULL,
                level int NOT NULL DEFAULT 1,
                attack_power int NULL DEFAULT 0,
                defense_power int NULL DEFAULT 0,
                raw_json text NULL COMMENT '原始数据',
                type_category int NULL COMMENT '状态类型分类',
                created_at datetime NOT NULL COMMENT '创建时间',
                updated_at datetime NULL COMMENT '更新时间',
                PRIMARY KEY (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='装备基础表'`,

            // 系统消息表
            `CREATE TABLE IF NOT EXISTS system_messages (
                id int NOT NULL AUTO_INCREMENT,
                senderName varchar(100) NOT NULL DEFAULT '系统',
                messageBody text NOT NULL COMMENT '消息体，存储完整的消息数据格式',
                messageType tinyint NOT NULL COMMENT '消息类型：1-个人消息，2-广播消息',
                created_at datetime NOT NULL COMMENT '创建时间',
                updated_at datetime NULL COMMENT '更新时间',
                PRIMARY KEY (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='系统消息表'`
        ];

        for (const table of tables) {
            try {
                await connection.execute(table);
                console.log('✅ 表创建成功');
            } catch (error) {
                if (error.code === 'ER_TABLE_EXISTS_ERROR') {
                    console.log('⚠️ 表已存在，跳过');
                } else {
                    console.error('❌ 表创建失败:', error.message);
                }
            }
        }

        console.log('🎉 所有表创建完成！');

    } catch (error) {
        console.error('❌ 创建表结构失败:', error);
    } finally {
        await connection.end();
    }
}

// 执行创建
createMySQL55CompatibleTables();
